package com.wxtf.platform.main.jcqkgl;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;

import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.AccessDataBaseUtil;

/**
 * <li>Title: emptongji_Action.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.sygl</li>
 * <li>Description: 生育情况统计表</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Aug 21, 2012 10:29:02 AM</li>
 * 
 * @author wangli
 * @version 1.0.0.0
 * 
 */
public class emptongji_Action extends LogicBase implements
		CommonsCallProvider {
	private static final Log log = LogFactory
			.getLog(emptongji_Action.class);

	public emptongji_Action(String jsonArray) {

	}

	@Override
	public Object run() {
		Object msg = "";
		try {
			if("queryPage".equals(this.getMethodName())) {//家庭信息
				msg = this.select(this.getLogicData());
			} else if("queryEmployee".equals(this.getMethodName())){
				msg = this.select_tem(this.getLogicData());//员工信息
			} else {
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if (msg == null || "[null]".equals(msg) || "null".equals(msg)) {
				msg = "\"\"";
			}
			if (msg.toString().startsWith("{")
					|| msg.toString().startsWith("[")) {
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch (Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(e, e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");

		Object[] params = null;
		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String type = jsonObject.getString("type");
			String orgIds = jsonObject.getString("orgIds");//机构ID串身份证
			String time1 = jsonObject.getString("time1");// 开始时间
			String time2 = jsonObject.getString("time2");// 结束时间
			StringBuffer buff = new StringBuffer();
			
			if("only".equals(type)) {// 独生子女费查询
				sql = "from trp_only_child_fee_management p LEFT JOIN tb_family_base f on f.tbfi_id=p.tbfi_id LEFT JOIN tb_employee e on f.tbe_id=e.tbe_id where 1=1";
				
				if(StringUtils.isNotBlank(time1)) {
					buff.append(" and trpocfm_ocr_time >= '").append(time1).append("'");
				}
				if(StringUtils.isNotBlank(time2)) {
					buff.append(" and trpocfm_ocr_time <='").append(time2).append(" 23:59:59'");
				}
			} else if("exceeds".equals(type)) {// 超生查询
				sql = " from trp_exceeds_children_penalty p LEFT JOIN tb_family_base f on f.tbfi_id=p.tbfi_id LEFT JOIN tb_employee e on f.tbe_id=e.tbe_id where 1 = 1 ";

				if(StringUtils.isNotBlank(time1)) {
					buff.append(" and trpecp_payment_time >= '").append(time1).append("'");
				}
				if(StringUtils.isNotBlank(time2)) {
					buff.append(" and trpecp_payment_time <='").append(time2).append(" 23:59:59'");
				}
			}
			
			if(StringUtils.isNotBlank(orgIds)){
				buff.append(" and f.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
			}
			// 排序
			String sortname = this.getRequest().getParameter("sortname");
			String sortorder = this.getRequest().getParameter("sortorder");
			if(sortname != null && sortname != null) {
				if(sortname.subSequence(0, 4).equals("tbfi"))
				{
					buff.append(" order by f.").append(sortname).append(" ").append(sortorder);
				}
				else
				{
					buff.append(" order by e.").append(sortname).append(" ").append(sortorder);
				}
			}
			sql += buff.toString();
		}
		
		String countSql = "select count(*) " + sql;
		String querySql = "select e.tbe_id,e.org_id,e.tbe_spell_code,e.tbe_idcard,e.tbe_work_permit,e.tbe_name,tbe_sex,e.tbe_age,e.tbe_birthday,e.tbe_position,e.tbe_native_palce,e.tbe_family_phone,e.tbe_address,e.tbe_is_again_birth,e.tbe_is_barrenness,e.tbe_is_late_marriage,tbe_is_late_birth," +
				"e.tbe_status,f.tbfi_id,f.tbe_spouse_id,f.tbfi_spouse_name,f.tbfi_spouse_sex,tbfi_spouse_age,f.tbfi_spouse_birthday,f.tbfi_spouse_idcard,f.tbfi_spouse_department,tbfi_spouse_position,f.tbfi_spouse_is_join,f.tbfi_phone,f.tbfi_mobile,f.tbfi_address,e.tbe_marital_status,e.tbe_marital_condition,f.tbfi_peo_num,f.tbfi_children_num,f.tbfi_children_mnum,f.tbfi_children_fnum,f.tbfi_memo,e.reserve1 " + sql;
		
		String[] fields = { "tbe_id", "org_id", "tbe_spell_code","tbe_idcard", "tbe_work_permit", "tbe_name", "tbe_sex",
				"tbe_age", "tbe_birthday","tbe_position", "tbe_native_palce", "tbe_family_phone", "tbe_address",  "tbe_is_again_birth",
				 "tbe_is_barrenness", "tbe_is_late_marriage","tbe_is_late_birth", "tbe_status","tbfi_id", "tbe_spouse_id","tbfi_spouse_name",
				 "tbfi_spouse_sex","tbfi_spouse_age", "tbfi_spouse_birthday", "tbfi_spouse_idcard", "tbfi_spouse_department",
				"tbfi_spouse_position", "tbfi_spouse_is_join", "tbfi_phone", "tbfi_mobile", "tbfi_address","tbe_marital_status",
				"tbe_marital_condition","tbfi_peo_num", "tbfi_children_num", "tbfi_children_mnum", "tbfi_children_fnum", "tbfi_memo","reserve1"};
		
		//System.out.println("fam:"+querySql);
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), params);
	}

	///查询员工信息
	public Object select_tem(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		Object[] params = null;

		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String type = jsonObject.getString("type");
			String orgIds = jsonObject.getString("orgIds");//机构ID串身份证
			String time1 = jsonObject.getString("time1");// 开始时间
			String time2 = jsonObject.getString("time2");// 结束时间
			StringBuffer buff = new StringBuffer();

			if("retire".equals(type)) {//退休父母奖励查询
				sql = "from tbe_only_child_record o LEFT JOIN tb_family_base f on o.tbfi_id=f.tbfi_id LEFT JOIN tb_employee e on f.tbe_id=e.tbe_id where o.reserve2<>'1' and e.tbe_employment_forms <> '31' and e.tbe_status not in('102-4','102-3') ";
				if(StringUtils.isBlank(time1)&&StringUtils.isBlank(time2))
				{
					buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 60 YEAR)");
					buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 50 YEAR)");
					buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 55 YEAR))");
				}
				if(StringUtils.isNotBlank(time1)&&StringUtils.isNotBlank(time2))
				{
					buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 60 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 60 YEAR)");//男职工
					buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 50 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 50 YEAR)");//女职工
					buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 55 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 55 YEAR))");//女干部
				}
				else
				{
					if(StringUtils.isNotBlank(time1)) {
						buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 60 YEAR)");//男职工
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 50 YEAR)");//女职工
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 55 YEAR))");//女干部
					}
					if(StringUtils.isNotBlank(time2)) {
						buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 60 YEAR)");//男职工
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 50 YEAR)");//女职工
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 55 YEAR))");//女干部
					}
				}
				if(StringUtils.isNotBlank(orgIds)){
					buff.append(" and e.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
				}
				// 排序
				String sortname = this.getRequest().getParameter("sortname");
				String sortorder = this.getRequest().getParameter("sortorder");
				if(sortname != null && sortname != null) {
					buff.append(" order by ").append(sortname).append(" ").append(sortorder);
				}
				sql += buff.toString();
			} 
			else if("retired".equals(type))
			{
				sql = "from tbe_only_child_record o LEFT JOIN tb_family_base f on o.tbfi_id=f.tbfi_id LEFT JOIN tb_employee e on f.tbe_id=e.tbe_id LEFT JOIN trp_only_child_retire_reward r on e.tbe_id=r.tbe_id where trpocrr_id <>'' ";
				if(StringUtils.isBlank(time1)&&StringUtils.isBlank(time2))
				{
					buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 60 YEAR)");
					buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 50 YEAR)");
					buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 55 YEAR))");
				}
				if(StringUtils.isNotBlank(time1)&&StringUtils.isNotBlank(time2))
				{
					buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 60 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 60 YEAR)");//男职工
					buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 50 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 50 YEAR)");//女职工
					buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 55 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 55 YEAR))");//女干部
				}
				else
				{
					if(StringUtils.isNotBlank(time1)) {
						buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 60 YEAR)");//男职工
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 50 YEAR)");//女职工
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 55 YEAR))");//女干部
					}
					if(StringUtils.isNotBlank(time2)) {
						buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 60 YEAR)");//男职工
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 50 YEAR)");//女职工
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 55 YEAR))");//女干部
					}
				}
				if(StringUtils.isNotBlank(orgIds)){
					buff.append(" and e.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
				}
				// 排序
				String sortname = this.getRequest().getParameter("sortname");
				String sortorder = this.getRequest().getParameter("sortorder");
				if(sortname != null && sortname != null) {
					buff.append(" order by ").append(sortname).append(" ").append(sortorder);
				}
				sql += buff.toString();
			}
		}
		
		String countSql = "select count(*) " + sql;
		String querySql = "select e.tbe_id,e.org_id,e.tbe_idcard,e.tbe_work_permit,e.tbe_employment_forms,e.tbe_name,e.tbe_former_name,e.tbe_spell_code,e.tbe_sex,e.tbe_age,e.tbe_birthday,e.tbe_nation,e.tbe_native_palce,e.tbe_birthplace,e.tbe_join_time,e.tbe_seniority,e.tbe_family_phone,e.tbe_mobile,e.tbe_address,e.tbe_marital_status,e.tbe_marital_condition,e.tbe_married_time,e.tbe_marriage_certificate,e.tbe_political_status,e.tbe_qualification,e.tbe_graduate_college,e.tbe_major,e.tbe_work_area,e.tbe_position,e.tbe_is_again_birth,e.tbe_is_barrenness,e.the_is_menopause,e.tbe_is_late_marriage,e.tbe_is_late_birth,e.tbe_status,e.tbe_ba_status,e.tbe_birth_control_measures,e.tbe_es_time,e.tbe_is_es,e.tbe_es_reason,e.tbe_children_status,e.tbe_order,e.tbe_memo,e.reserve1,e.reserve2,e.reserve3,e.reserve4,e.reserve5,e.create_by,e.create_time,e.last_modify_by,e.last_modify_time " + sql;
		String[] fields = {"tbe_id","org_id","tbe_idcard","tbe_work_permit",
				"tbe_employment_forms","tbe_name","tbe_former_name","tbe_spell_code","tbe_sex","tbe_age","tbe_birthday","tbe_nation","tbe_native_palce",
				"tbe_birthplace","tbe_join_time","tbe_seniority","tbe_family_phone","tbe_mobile","tbe_address","tbe_marital_status","tbe_marital_condition",
				"tbe_married_time","tbe_marriage_certificate","tbe_political_status","tbe_qualification","tbe_graduate_college","tbe_major","tbe_work_area",
				"tbe_position","tbe_is_again_birth","tbe_is_barrenness","the_is_menopause","tbe_is_late_marriage","tbe_is_late_birth","tbe_status","tbe_ba_status",
				"tbe_birth_control_measures","tbe_es_time","tbe_is_es","tbe_es_reason","tbe_children_status","tbe_order","tbe_memo",
				"reserve1","reserve2","reserve3","reserve4","reserve5","create_by","create_time","last_modify_by","last_modify_time"};
		//System.out.println("emp:"+querySql);
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), params);
	}

	
	@Override
	public Object insert(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object delete(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
}